package com.fatiny.util;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import com.fatiny.core.common.Config;
import com.fatiny.core.db.ExcelAssistEntity;
import com.fatiny.core.db.ExcelAssistProperties;
import com.fatiny.core.db.ExcelEntity;
import com.fatiny.core.db.ExcelProperties;

/**
 * excel 工具类
 */
public class ExcelUtils {
	
	private static final org.slf4j.Logger log = org.slf4j.LoggerFactory.getLogger(ExcelUtils.class);
	
	
	// 常亮，用作第一种模板类型，如下图
	private static final int HEADER_VALUE_TYPE_Z = 1;
	// 第二种模板类型，如下图
	private static final int HEADER_VALUE_TYPE_S = 2;

	/**
	 * 文件过滤
	 */
	private static boolean fileNameFileter(File file) {
		boolean endsWith = false;
		if (file != null) {
			String fileName = file.getName();
			endsWith = fileName.endsWith(".xls") || fileName.endsWith(".xlsx");
		}
		return endsWith;
	}

	/**
	 * 获取表头行
	 */
	private static Row getHeaderRow(Sheet sheet, int index) {
		Row headerRow = null;
		if (sheet != null) {
			headerRow = sheet.getRow(index);
		}
		return headerRow;
	}

	/**
	 * 获取表格中单元格的value
	 */
	private static Object getCellValue(Row row, int cellIndex, FormulaEvaluator formula) {
		Cell cell = row.getCell(cellIndex);
		if (cell != null) {
			CellType callType = cell.getCellTypeEnum();
			if (callType == CellType.STRING) {
				return cell.getRichStringCellValue().getString();
			}else if (callType == CellType.NUMERIC) {
				if (DateUtil.isCellDateFormatted(cell)) {
					return cell.getDateCellValue().getTime();
				} else {
					double val = cell.getNumericCellValue();
					return Double.valueOf(val).intValue();
				}
			}else if (callType == CellType.BOOLEAN) {
				return cell.getBooleanCellValue();
			}else {
				return 0;
			}
		}
		return null;
	}

	/**
	 * 获取表头value
	 */
	private static String getHeaderCellValue(Row headerRow, int cellIndex, int type) {
		Cell cell = headerRow.getCell(cellIndex);
		String headerValue = null;
		if (cell != null) {
			// 第一种模板类型
			if (type == HEADER_VALUE_TYPE_Z) {
				headerValue = cell.getRichStringCellValue().getString();
				int l_bracket = headerValue.indexOf("（");
				int r_bracket = headerValue.indexOf("）");
				if (l_bracket == -1) {
					l_bracket = headerValue.indexOf("(");
				}
				if (r_bracket == -1) {
					r_bracket = headerValue.indexOf(")");
				}
				headerValue = headerValue.substring(l_bracket + 1, r_bracket);
			} else if (type == HEADER_VALUE_TYPE_S) {
				// 第二种模板类型
				headerValue = cell.getRichStringCellValue().getString();
			}
		}
		return headerValue;
	}

//	/**
//	 * 读取excel表格 
//	 */
//	public static String readExcle(File file, int headerIndex, int headType) {
//		Map<String, Object> result = new HashMap<>();
//		if (!fileNameFileter(file)) {
//			return null;
//		} else {
//			try {
//				List<Map<String, Object>> lists = null;
//				// 加载excel表格
//				WorkbookFactory wbFactory = new WorkbookFactory();
//				Workbook workBook = wbFactory.create(file);
//				int sheetsNumber = workBook.getNumberOfSheets();
//				Sheet sheet = null;
//		        for (int i = 0; i < sheetsNumber; i++) {
//		        	lists = new ArrayList<Map<String, Object>>();
//		        	sheet = workBook.getSheetAt(i);
//		        	String sheetName = sheet.getSheetName();
//		        	System.out.println("sheetName:{}"+ sheet.getSheetName());
//		        	if (sheetName.startsWith("Db")) {
//		             	readContext(headerIndex, headType, lists, workBook, sheet);
//		             	result.put("entityName", sheetName);
//		             	result.put("entityBeans", lists);
//		     		}
//				}
//			} catch (Exception e) {
//				e.printStackTrace();
//			}
//		}
//		return  JSON.toJSONString(result);
//	}
	
//	/**
//	 * 读取excel表格 
//	 * @param headerIndex 0代表是第一行为保存到数据库或者实体类的表头，一般为英文的字符串，
//	 * @oaram headType 2代表是第二种模板
//	 */
//	public static List<ExcelEntity> readExcle2(String path, int headerIndex, int headType) {
//		File file = new File(path);
//		List<ExcelEntity> entitys = new ArrayList<ExcelEntity>();
//		if (!fileNameFileter(file)) {
//			return null;
//		} else {
//			try {
//				List<Map<String, Object>> lists = null;
//				// 加载excel表格
//				WorkbookFactory wbFactory = new WorkbookFactory();
//				Workbook workBook = wbFactory.create(file);
//				int sheetsNumber = workBook.getNumberOfSheets();
//				Sheet sheet = null;
//				Map<String, Object> result = null;
//		        for (int i = 0; i < sheetsNumber; i++) {
//		        	result = new HashMap<>();
//		        	lists = new ArrayList<Map<String, Object>>();
//		        	sheet = workBook.getSheetAt(i);
//		        	String sheetName = sheet.getSheetName();
//		        	if (sheetName.startsWith("Db")) {//excel内容转Javabean
//		             	readContext(headerIndex, headType, lists, workBook, sheet);
//		             	result.put("entityName", sheetName);
//		             	result.put("entityBeans", lists);
//		             	result.put("entityIndexs", lists);
//
//		             	JSONObject jsonBoj = new JSONObject(result);
//		             	ExcelEntity entity = JSON.parseObject(jsonBoj.toJSONString(), new TypeReference<ExcelEntity>() {});
//		             	entitys.add(entity);
//		     		}
//				}
//			} catch (Exception e) {
//				e.printStackTrace();
//			}
//		}
//		return entitys;
//	}
	
	/**
	 * 读取excel表格 
	 * @param headerIndex 0代表是第一行为保存到数据库或者实体类的表头，一般为英文的字符串，
	 * @oaram headType 2代表是第二种模板
	 */
	public static List<ExcelEntity> readExcle3(String path, int headerIndex, int headType) {
		File file = new File(path);
		List<ExcelEntity> entitys = new ArrayList<ExcelEntity>();
		if (!fileNameFileter(file)) {
			return null;
		} else {
			try {
				// 加载excel表格
				Workbook workBook = WorkbookFactory.create(file);
				int sheetsNumber = workBook.getNumberOfSheets();
				Sheet sheet = null;
				ExcelEntity entity = null;
		        for (int i = 0; i < sheetsNumber; i++) {
		        	sheet = workBook.getSheetAt(i);
		        	String sheetName = sheet.getSheetName();
		        	if (sheetName.startsWith(Config.sheetFilter)) {//excel内容转Javabean
		        		entity = new ExcelEntity(sheetName);
		        		readContext2(headerIndex, headType, entity, workBook, sheet);
		             	entitys.add(entity);
		     		}
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return entitys;
	}

//	/**
//	 * 读excel内容
//	 * @date 2019年9月16日上午10:16:13
//	 */
//	private static void readContext(int headerIndex, int headType, List<Map<String, Object>> lists, Workbook wb, Sheet sheet) {
//		// 读取表头行
//		Row headerRow = getHeaderRow(sheet, headerIndex);
//		// 读取数据
//		FormulaEvaluator formula = wb.getCreationHelper().createFormulaEvaluator();
//		log.info("sheet.getLastRowNum:{}", sheet.getLastRowNum());
//		for (int r = headerIndex + 1; r <= sheet.getLastRowNum(); r++) {
//			Row dataRow = sheet.getRow(r);
//			Map<String, Object> map = new HashMap<String, Object>();
//			log.info("dataRow.getLastCellNum:{}", dataRow.getLastCellNum());
//			for (int h = 0; h < dataRow.getLastCellNum(); h++) {
//				// 表头为key
//				String key = getHeaderCellValue(headerRow, h, headType);
//				// 数据为value
//				Object value = getCellValue(dataRow, h, formula);
//				if (!key.equals("") && !key.equals("null") && key != null) {
//					map.put(key, value);
//				}
//			}
//			lists.add(map);
//		}
//	}
	
	/**
	 * 读excel内容, 生成Javabean
	 *  字段		类型		描述		是否主键
	 *	field	type	desc	primary
	 *
	 * @date 2019年9月16日上午10:16:13
	 */
	private static void readContext2(int headerIndex, int headType, ExcelEntity entity, Workbook wb, Sheet sheet) {
		int contentType = 1;
		// 读取表头行
		Row headerRow = getHeaderRow(sheet, headerIndex);
		// 读取数据
		FormulaEvaluator formula = wb.getCreationHelper().createFormulaEvaluator();
		List<Object> lists = new ArrayList<>();
		for (int r = headerIndex + 1; r <= sheet.getLastRowNum(); r++) {
			Row dataRow = sheet.getRow(r);
			if (dataRow == null) {
				contentType ++;
				r += 2;
				headerIndex = r;//重新定位表头行
				headerRow = getHeaderRow(sheet, headerIndex);
				continue;
			}
			if (contentType == 1) {
				Map<String, Object> map = extracted(headType, headerRow, formula, dataRow);
				lists.add(map);
			}else {
				//TODO 普通情况下，数据表结构越简单越好，但是这个框架需要支持复杂结构。
				// 暂时没有想回到好方法，只能先这样替代。
				Map<String, Object> map = extracted(headType, headerRow, formula, dataRow);
				String entityName = (String)map.get("entityName");
				String properties = (String)map.get("properties");
				String desc = (String)map.get("desc");
				
				ExcelAssistProperties ep = new ExcelAssistProperties();
				ep.setProperties(properties);
				ep.setDesc(desc);
				entity.addAssistEntity(entityName, ep);
			}
		}
		JSONArray jArray = new JSONArray(lists);
		List<ExcelProperties> excelBeans = JSON.parseObject(jArray.toJSONString(), new TypeReference<List<ExcelProperties>>() {});
		entity.setEntityBeans(excelBeans);
		log.info(" =====> entity:{}",entity);
	}

	private static Map<String, Object> extracted(int headType, Row headerRow, FormulaEvaluator formula, Row dataRow) {
		Map<String, Object> map = new HashMap<String, Object>();
		for (int h = 0; h < dataRow.getLastCellNum(); h++) {
			// 表头为key
			String key = getHeaderCellValue(headerRow, h, headType);
			// 数据为value
			Object value = getCellValue(dataRow, h, formula);
			if (key == null) //行末
				continue;
			if (!key.equals("") && !key.equals("null") && key != null) {
				map.put(key, value);
			}
		}
		return map;
	}
	
	
	/**
	 * 生成一个excel
	 * @return void  
	 * @date 2019年9月16日上午10:54:03
	 */
	public static void writeExcel(List<ExcelEntity> excelEntitys) {
		 //  创建一个工作簿
	    HSSFWorkbook wb = new HSSFWorkbook();
	    //  创建一个工作表
	    HSSFSheet sheet = wb.createSheet();
	    
	    //取第一个生成
	    ExcelEntity entity = excelEntitys.get(0);
	    wb.setSheetName(0, entity.getEntityName());
	    
	    //创建行, 前两行用于字段以及备注, 从第三行开始写入数据
	    //创建列, 固定4列
	    int size = entity.getEntityBeans().size();
	    
	    //生成第一行
	    HSSFRow row = sheet.createRow(0);
    	row.createCell(0).setCellValue("字段");
    	row.createCell(1).setCellValue("类型");
    	row.createCell(2).setCellValue("描述");
    	row.createCell(3).setCellValue("是否主键,第一个主键为1,第二个为2,依次累加,程序根据从小到大顺序组装mongo_id");
	    
    	//生成第二行, 带背景色
    	//创建单元格格式
	    HSSFCellStyle css = wb.createCellStyle();
	    css.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
	    css.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
	    css.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	    
	    //设置字体
//	    HSSFFont font = wb.createFont();
//	    font.setFontHeightInPoints((short) 10);
//	    font.setFontName("微软雅黑");
//	    font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
//	    css.setFont(font);
    	
    	List<String> model = new ArrayList<>();
    	model.add("field");
    	model.add("type");
    	model.add("desc");
    	model.add("primary");
    	row = sheet.createRow(1);
    	for (int i = 0; i < model.size(); i++) {
    		HSSFCell cell = row.createCell(i);
    		cell.setCellValue(model.get(i));
    		cell.setCellStyle(css);
		}
	    
    	//生成主体内容, 第三行开始
    	for (int j = 0; j < size; j++) {
    		row = sheet.createRow(j + 2);
    		ExcelProperties excelBean = entity.getEntityBeans().get(j);
    		HSSFCell cell = row.createCell(0);
 		    cell.setCellValue(excelBean.getField());
 		    
 		    cell = row.createCell(1);
 		    cell.setCellValue(excelBean.getType());
 		    
 		    cell = row.createCell(2);
 		    cell.setCellValue(excelBean.getDesc());
 		    
 		    cell = row.createCell(3);
 		    cell.setCellValue(excelBean.getPrimary());
		}
    	
    	//  写文件
	    FileOutputStream fos;
		try {
			fos = new FileOutputStream("src/main/resources/db.xls");
			wb.write(fos);
			fos.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	  
		
	}
	
	
	/**
	 * 生成一个excel
	 * @return void  
	 * @date 2019年9月16日上午10:54:03
	 */
	public static void writeExcel2(String outPutExcel ,List<ExcelEntity> excelEntitys) {
		
		log.info("生成sheet数量:{}", excelEntitys.size());
		
		 //  创建一个工作簿
	    HSSFWorkbook wb = new HSSFWorkbook();
	    //  创建一个工作表
	    HSSFSheet sheet = null;
	    for (int i = 0; i < excelEntitys.size(); i++) {
	    	try {
	    		ExcelEntity entity = excelEntitys.get(i);
		    	//log.info("当前sheetIndex:{}, sheetName:{}", i, entity.getEntityName());
		    	sheet = wb.createSheet();
		    	//wb.setActiveSheet(i);
		    	wb.setSheetName(i, entity.getEntityName());
		    	writeContext(wb, sheet, entity);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
    	//  写文件
	    FileOutputStream fos;
		try {
			fos = new FileOutputStream(outPutExcel);
			wb.write(fos);
			fos.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		log.info("生成excel成功");
	}

	private static void writeContext(HSSFWorkbook wb, HSSFSheet sheet, ExcelEntity entity) {
		int index = 0; //从下标0开始
		//生成索引注释
    	HSSFRow row = sheet.createRow(index);
    	row.createCell(0).setCellValue("索引名称");
    	row.createCell(1).setCellValue("字段组, 需要跟表字段相同");
    	row.createCell(2).setCellValue("所支持的操作选项");
    	
    	//生成索引列
    	row = sheet.createRow(++index);
    	row.createCell(0).setCellValue("indexName");
    	row.createCell(1).setCellValue("fieldArr");
    	row.createCell(2).setCellValue("options");
    	
    	//默认生成playerId 作为索引
    	row = sheet.createRow(++index);
    	String indexName = entity.getEntityName() + "-playerId";
    	row.createCell(0).setCellValue(indexName);
    	row.createCell(1).setCellValue("playerId");
    	row.createCell(2).setCellValue("unique,background");
    	
    	//空行
    	row = sheet.createRow(++index);
	    //结束生成索引注释
		
		//生成第一行
		row = sheet.createRow(++index);
		row.createCell(0).setCellValue("字段");
		row.createCell(1).setCellValue("类型");
		row.createCell(2).setCellValue("描述");
		row.createCell(3).setCellValue("是否主键,第一个主键为1,第二个为2,依次累加,程序根据从小到大顺序组装mongo_id");
		
		//生成第二行, 带背景色
		//创建单元格格式
		HSSFCellStyle css = wb.createCellStyle();
		css.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
		css.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
		css.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		
		//设置字体
	    //HSSFFont font = wb.createFont();
	    //font.setFontHeightInPoints((short) 10);
	    //font.setFontName("微软雅黑");
	    //font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
	    //css.setFont(font);
		
		List<String> model = new ArrayList<>();
		model.add("field");
		model.add("type");
		model.add("desc");
		model.add("primary");
		row = sheet.createRow(++index);
		for (int j = 0; j < model.size(); j++) {
			HSSFCell cell = row.createCell(j);
			cell.setCellValue(model.get(j));
			cell.setCellStyle(css);
		}
		
		//创建行, 前两行用于字段以及备注, 从第三行开始写入数据
		//创建列, 固定4列
		int size = entity.getEntityBeans().size();
		//生成主体内容, 第三行开始
		int interval = ++index;
		for (int j = 0; j < size; j++) {
			row = sheet.createRow(j + interval);
			ExcelProperties excelBean = entity.getEntityBeans().get(j);
			HSSFCell cell = row.createCell(0);
		    cell.setCellValue(excelBean.getField());
		    
		    cell = row.createCell(1);
		    cell.setCellValue(excelBean.getType());
		    
		    cell = row.createCell(2);
		    cell.setCellValue(excelBean.getDesc());
		    
		    cell = row.createCell(3);
		    cell.setCellValue(excelBean.getPrimary());
		}
	}
}

















